"""
    功能：向tb_areas表中插入数据
    
sql例子：
INSERT INTO `tb_areas` VALUES ('110000', '北京市', NULL);
INSERT INTO `tb_areas` VALUES ('110100', '北京市', '110000');
INSERT INTO `tb_areas` VALUES ('110101', '东城区', '110100');
INSERT INTO `tb_areas` VALUES ('110102', '西城区', '110100');
INSERT INTO `tb_areas` VALUES ('110105', '朝阳区', '110100');

sql语句放在meiduo_mall.apps.area.areas.sql中

"""
import pymysql
import re
import os

# 数据库配置信息
DB_CONFIG = {
    'host': '172.19.251.204',  # WSL 2 的 IP 地址
    'user': 'meiduo_user',
    'password': '123456',      # meiduo_user 的密码
    'port': 3306,
    'database': 'meiduo_mall'  # 指定要使用的数据库
}

try:
    # 连接数据库
    connection = pymysql.connect(**DB_CONFIG)
    with connection.cursor() as cursor:
        # 获取脚本的父目录
        base_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
        # 构建 SQL 文件的绝对路径
        sql_file_path = os.path.join(base_dir, 'meiduo_mall/apps/areas/areas.sql')
        # 读取 sql 文件
        with open(sql_file_path, 'r', encoding='utf8') as f:
            sql = f.read()
        
        # 替换 sql 语句中的换行符
        sql = re.sub(r'\n', '', sql)
        
        # 分割 SQL 语句
        sql_commands = sql.split(';')
        for command in sql_commands:
            if command.strip():
                cursor.execute(command)
    
    # 提交事务
    connection.commit()
    print("SQL 语句执行成功")
except pymysql.Error as e:
    print(f"执行 SQL 语句时出错: {e}")
    if 'connection' in locals() and connection:
        connection.rollback()
finally:
    if 'connection' in locals() and connection:
        connection.close()